 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.Date;
 import java.text.DateFormat;
 import java.text.SimpleDateFormat;
 import java.util.Iterator;

import thothCommonClasses.*;

public class dataaccess {

public static void main (String[] args) {
java.util.Date date = new java.util.Date();
 
java.sql.Date sqlDate = new java.sql.Date(date.getTime());;  

Parameters param = new Parameters();
param.signalType = Data.SignalType.Wifi.toString();
param.latitudeMin = 30;
param.longitudeMin = 30;
param.latitudeMax = 50;
param.longitudeMax = 50;

getInformation(param);
}

 public static void StoreInformation (Date time, String type, double strength, double latitude, double longitude, String vendor, String number)
 {
 
 Connection conn = null;
 
    try 
{
String username = "root";
String password = "project";
String url = "jdbc:mysql://localhost:3306/signalinformation";
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(url,username,password);
Statement st = conn.createStatement();
String insert = "INSERT INTO signalinfo(time,type,strength,latitude,longitude,vendor,number) VALUES ('"+time+"','"+type+"','"+strength+"','"+latitude+"','"+longitude+"','"+vendor+"','"+number+"')";
System.out.println(insert);
st.executeUpdate(insert);

System.out.println("Database connection established");

}

catch(Exception e)
{
System.err.println("Cannot connect to database server " + e.getMessage());
}

 
}//function


public static DataList getInformation(Parameters param)
{

 Connection conn = null;
 DataList dl = new DataList();
   try 
{
String username = "root";
String password = "project";
String url = "jdbc:mysql://localhost:3306/signalinformation";
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(url,username,password);
Statement st = conn.createStatement();

float longMin = param.longitudeMin;
float longMax = param.longitudeMax;
float latiMin = param.latitudeMin;
float latiMax = param.latitudeMax;

String get = "select * from signalinfo where type = '" + param.signalType +"'" 
									+" and " + longMin + " < longitude and longitude < "+ longMax
									+" and " + latiMin + " < latitude and latitude < "+ latiMax+";";

System.out.println(get);
ResultSet rs = st.executeQuery(get);

Data d = new Data();
myCipher c = new myCipher();
byte[] encryptionBytes = null;
while (rs.next())
      {
        java.sql.Date dt = rs.getDate(1);
        String type = rs.getString(2);
        float strength = rs.getFloat(3);
        float lat = rs.getFloat(4);
        float  lon = rs.getFloat(5);
        String vendor = rs.getString(6);
        String number = rs.getString(7);
		
		d.time = dt;
		d.type = type;
		d.strength = strength;
		d.latitude = lat;
		d.longitude = lon;
		d.vendor = vendor;
		d.number = number;
		
        
        System.out.println(
          "    " +
          dt + " " +
          type + " " +
        strength  + " " +
          lat + " " +
         lon + " " + vendor + " "+ number );
        
         encryptionBytes =  c.encrypt(d.toString());
         
         for (int i = 0; i < encryptionBytes.length; i ++) {
         	System.out.format(" 0x%02x" , encryptionBytes[i]);    
         	if(i%10 == 9)
         		System.out.println("");   	
         }
         
         dl.list1.add(encryptionBytes);
      }
      rs.close();
      st.close();
      
   System.out.println(dl.list.size());
	for(Iterator i = dl.list.iterator();i.hasNext();){System.out.println(i.next());}     
	System.out.println("Database connection established");
	}

	catch(Exception e)
	{
	System.err.println("Cannot connect to database server " + e.getMessage());
	}
   return dl;

}

}
